﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace FantastPlatform.Decision.UI.Service
{
    public partial class AreaHanppenCaseService :BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            LoginEmployee = Session["User"] as FantastPlatform.Framework.Model.Employee;
            List<AreaCaseCount> counts = new List<AreaCaseCount>();
            string sql = "";
            switch (Action)
            {
                case "Search":
                    DateTime startTime = DateTime.Now;
                    DateTime endTime = DateTime.Now;
                    DateTime now = DateTime.Now;
                    if (Convert.ToInt32(DateTime.Now.DayOfWeek) == 0)
                    {
                        startTime = now.Date.AddDays(-13);
                        endTime = now.Date.AddDays(-7);
                    }
                    else
                    {
                        startTime = now.Date.AddDays(-Convert.ToInt32(now.DayOfWeek) - 6);
                        endTime = now.Date.AddDays(-Convert.ToInt32(now.DayOfWeek));
                    }
                    sql = "select substring(ps.BaseName,6,2),null,null,count(*) from dbo.CaseMainLine as c inner join dbo.ResponsibilityArea as ps on c.ResponsibilityAreaId = ps.id inner join dbo.CaseType as t on c.Casetypeid = t.id where CaseHappendTimeLower >= '" + startTime + "' and CaseHappendTimeLower<='" + endTime + "' and ps.BaseName is not null and (t.name = '诈骗案' or t.name = '入室盗窃案' or t.name = '抢夺案' or t.name = '抢劫案' or t.name = '盗窃汽车案') group by ps.BaseName order by count(*) desc";
                    break;
                case "SearchByCondition":
                    DataView dataView = JsonToEntity<DataView>(RepairString(Data));
                    if (dataView.Ttime == "派出所")
                    {
                        sql = "select substring(ps.BaseName,6,2),null,null,count(*) from dbo.CaseMainLine as c inner join dbo.ResponsibilityArea as ps on c.ResponsibilityAreaId = ps.id inner join dbo.CaseType as t on c.Casetypeid = t.id ";
                        sql+=" where CaseHappendTimeLower >= '" + dataView.StartTime + "' and CaseHappendTimeLower<='" + dataView.EndTime  + "' and ps.BaseName is not null and ( t.name is null ";
                        if(dataView.zpcase!=null)
                            sql+=" or t.name = '诈骗案'";
                        if(dataView.rscase!=null)
                            sql+=" or t.name = '入室盗窃案'";
                        if(dataView.qdcase !=null)
                            sql+=" or t.name = '抢夺案'";
                        if(dataView.qjcase !=null)
                            sql+=" or t.name = '抢劫案'";
                        if(dataView.dqcase!=null)
                            sql+=" or t.name = '盗窃汽车案'";

                        sql+= ") group by ps.BaseName order by count(*) desc";
                    }
                    else if (dataView.Ttime == "责任区")
                    {
                        sql = "select null, substring(ps.Name,6,14), null,count(*)from dbo.CaseMainLine as c inner join dbo.ResponsibilityArea as ps on c.ResponsibilityAreaId = ps.id inner join dbo.CaseType as t on c.Casetypeid = t.id";
                        sql += " where CaseHappendTimeLower >= '" + dataView.StartTime + "' and CaseHappendTimeLower<='" + dataView.EndTime + "' and ps.BaseName is not null and ( t.name is null ";
                        if (dataView.zpcase != null)
                            sql += " or t.name = '诈骗案'";
                        if (dataView.rscase != null)
                            sql += " or t.name = '入室盗窃案'";
                        if (dataView.qdcase != null)
                            sql += " or t.name = '抢夺案'";
                        if (dataView.qjcase != null)
                            sql += " or t.name = '抢劫案'";
                        if (dataView.dqcase != null)
                            sql += " or t.name = '盗窃汽车案'";
                        sql+=" ) group by ps.Name order by count(*) Desc";
                    }
                    else if (dataView.Ttime == "街道")
                    {
                        sql = " select top 50 null,substring(ps.Name,6,14),s.name,count(*) from dbo.CaseMainLine as c inner join dbo.Street as s on c.CraStreetId = s.id inner join dbo.ResponsibilityArea as ps on c.ResponsibilityAreaId = ps.id inner join dbo.CaseType as t on c.Casetypeid = t.id ";
                        sql += " where CaseHappendTimeLower between '2011-1-1' and '2011-11-01' and s.Name is not null and (t.name is null ";
                        if (dataView.zpcase != null)
                            sql += " or t.name = '诈骗案'";
                        if (dataView.rscase != null)
                            sql += " or t.name = '入室盗窃案'";
                        if (dataView.qdcase != null)
                            sql += " or t.name = '抢夺案'";
                        if (dataView.qjcase != null)
                            sql += " or t.name = '抢劫案'";
                        if (dataView.dqcase != null)
                            sql += " or t.name = '盗窃汽车案'";
                        sql += " ) group by s.Name  ,ps.name order by count(*) Desc";
                    }
                    break;
            }
            string conStr = ConfigurationManager.AppSettings["Case"];
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand command = new SqlCommand(sql, con);
            con.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string station = reader[0].ToString();
                string responsibilityArea = reader[1].ToString();
                string street = reader[2].ToString();
                string caseCount = reader[3].ToString();

                if (!string.IsNullOrEmpty(responsibilityArea))
                {
                    station = responsibilityArea.Substring(0, 2);
                    responsibilityArea = responsibilityArea.Split('所')[1];
                }
                counts.Add(new AreaCaseCount()
                {
                    Station = station,
                    ResponsibilityArea = responsibilityArea,
                    Street = street,
                    CaseCount = caseCount
                });
            }
            ReturnList(counts.Count, counts);
        }
    }
    public class AreaCaseCount 
    {
        public string Station { get; set; }
        public string ResponsibilityArea { get; set; }
        public string Street { get; set; }
        public string CaseCount { get; set; }
    }
    public class DataView
    {
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
        public string Ttime { get; set; }
        public string rscase { get; set; }
        public string dqcase { get; set; }
        public string qjcase { get; set; }
        public string qdcase { get; set; }
        public string zpcase { get; set; }
    }
}